package com.bank.service;

import java.math.BigInteger;
import java.security.SecureRandom;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;
import java.util.UUID;
import javax.annotation.Resource;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bank.domain.Account;
import com.bank.domain.AccountTypes;
import com.bank.domain.Currency;
import com.bank.domain.Customer;
import com.bank.domain.Transaction;



@Service("accountService")
@Transactional
public class AccountService {

	protected static Logger logger = Logger.getLogger("service");

	@Resource(name="hashingService")
	private HashingService hashingService;
	
	@Resource(name="sessionFactory")
	private SessionFactory sessionFactory;
	
	@Resource(name="customerService")
	private CustomerService customerService;
	
	@Resource(name="transactionService")
	private TransactionService transactionService;
	
	public Object getUserAccounts() {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("SELECT accounts.ACCOUNT_HASH, accounts.ACCOUNT_IDENTITY, accounts.AMOUNT, accounts.UPDATED_AT, currency.CURRENCY_NAME , customeraccount.CUSTOMER_ID FROM accounts INNER JOIN currency ON accounts.CURRENCY_ID = currency.CURRENCY_ID INNER JOIN customeraccount ON accounts.ACCOUNT_ID = customeraccount.ACCOUNT_ID WHERE customeraccount.CUSTOMER_ID="+customerService.getCurrentId());
		System.out.println("Query is: " +query);
		try {
			
	    	return query.list();
		} catch (Exception e) {
			return null;
		}		
	
	}	
	
	
	
	public List<AccountTypes> getAccountTypes(){
		Session session = sessionFactory.getCurrentSession();
		// Create a Hibernate query (HQL)
		Query query = session.createQuery(" FROM AccountTypes");
		// Retrieve all
		return query.list();		
	}
	
	public List<Currency> getCurrencies(){
		Session session = sessionFactory.getCurrentSession();
		// Create a Hibernate query (HQL)
		Query query = session.createQuery(" FROM Currency");
		// Retrieve all
		return query.list();		
	}	
	
	
	public Boolean add(Account account) {
		logger.debug("Adding new account");
		// Retrieve session from Hibernate
		Session session = sessionFactory.getCurrentSession();
		SecureRandom random = new SecureRandom();
		java.util.Calendar cal = java.util.Calendar.getInstance(); 
		java.sql.Date timeNow = new Date(cal.getTimeInMillis());
		account.setAccountHash(new BigInteger(130, random).toString(32));
		account.setAccountId(5);
		account.setAccountIdentity("LV11VIAB"+hashingService.GenerateRandomNumber(13));
		account.setAccountTypeId(account.getAccountTypeId());
		account.setAmount(1000.00);
		account.setCurrencyId(account.getCurrencyId());
		account.setCreatedAt(timeNow);
		account.setUpdatedAt(timeNow);
		session.save(account);
		session.refresh(account);
		int userId = customerService.getCurrentId();
		int accountId = account.getAccountId();
		Query query = session.createSQLQuery("INSERT INTO customeraccount (CUSTOMER_ID,ACCOUNT_ID ) VALUES ( "+userId+", "+accountId+" )");
		System.out.println(query);
		query.executeUpdate();
		return true;

	}	
	
	//returns account ID by account number
	public Integer accountByNumber(String accountNr) {
		Session session = sessionFactory.getCurrentSession();
		Query account = session.createSQLQuery("SELECT ACCOUNT_ID FROM accounts WHERE ACCOUNT_IDENTITY = '" +accountNr+"'");
		logger.debug("This is sql:  "+ account);
		Object ll = account.uniqueResult();
		if(ll==null){return null;}
		Integer nr = Integer.parseInt(ll.toString());
		return nr;
	}
	
	public boolean doTransaction(Transaction transaction) {
		double TAX = 0.15;
		String BANK_ACCOUNT = "LV11VIAB1111111111111";		
		Transaction toBank = new Transaction();
		toBank.setAccountFrom(transaction.getAccountFrom());
		toBank.setAccountTo(BANK_ACCOUNT);
		toBank.setCustomerId(customerService.getCurrentId());
		toBank.setTransactionAmount(TAX);
		java.util.Date date= new java.util.Date();
		toBank.setTransactionDate(new Timestamp(date.getTime()));
		toBank.setTransactionDetails("TAX FROM " + transaction.getAccountFrom() + " AT " + new Timestamp(date.getTime()).toString());
		toBank.setTransactionHash("");
		toBank.setTransactionStatusId(1);
		transactionService.recordTransaction(toBank);

		logger.debug("Do transaction");
		// Retrieve session from Hibernate
		Session session = sessionFactory.getCurrentSession();
		
		String accountTo = transaction.getAccountTo();
		String accountFrom = transaction.getAccountFrom();
		double amount = transaction.getTransactionAmount(); 

		Query tax_in = session.createQuery("UPDATE Account SET AMOUNT=AMOUNT+"+TAX+" WHERE ACCOUNT_ID='" + accountByNumber(BANK_ACCOUNT) + "'");
		tax_in.executeUpdate();

		Query tax_out = session.createQuery("UPDATE Account SET AMOUNT=AMOUNT-"+TAX+" WHERE ACCOUNT_ID='" + accountByNumber(accountFrom) + "'");
		tax_out.executeUpdate();
		
		Query from = session.createQuery("UPDATE Account SET AMOUNT=AMOUNT+"+amount+" WHERE ACCOUNT_ID=" + accountByNumber(accountTo));
		from.executeUpdate();
		
		Query to = session.createQuery("UPDATE Account SET AMOUNT=AMOUNT-"+amount+" WHERE ACCOUNT_ID=" + accountByNumber(accountFrom));
		if(to.executeUpdate() == 1){
			return true;
		}
		else{
			return false;
		}
		
		

	}	
	
	
	public Double fundByAccountIdentity(String AccountIdentity){
		Session session = sessionFactory.getCurrentSession();
		Query account = session.createSQLQuery("SELECT AMOUNT FROM accounts WHERE ACCOUNT_IDENTITY = '" +AccountIdentity+"'");
		logger.debug("This is sql:  "+ account);
		Object ll = account.uniqueResult();
		if(ll==null){return null;}		
		Double nr = Double.parseDouble(ll.toString());
		return nr;
	}
	
	public Integer findOwnerByAccontIdentity(String AccountIdentity){
		Session session = sessionFactory.getCurrentSession();
		Query account = session.createSQLQuery("SELECT customeraccount.CUSTOMER_ID FROM customeraccount, accounts WHERE customeraccount.ACCOUNT_ID = accounts.ACCOUNT_ID AND accounts.ACCOUNT_IDENTITY = '"+AccountIdentity+"'");
		Object ll = account.uniqueResult();
		if(ll==null){return null;}	
		logger.debug("This is sql:  "+ account);
		return Integer.parseInt(ll.toString());
	}	
	
	
}
